import TEST.Head.数据读写 as dtR
import path
import re

PATH = path.Path().fp

class readyData():


    def xls_path(self):
        return PATH + r'TEST\Asset\财务理财系统web自动化测试用例.xls'

    def Login(self):
        lis = []
        result = []
        # 备表
        data_path = self.xls_path()
        data = dtR.xls(data_path, 1)
        # 读表
        xls = data.read(2)
        # 提取有效数据
        for i in xls:
            lis.append([i[4], i[5]])
        # 清理数据格式并重新封装
        for i in lis:
            temp = []
            # 通过re表达式过滤内容
            x = re.sub('[输入密码错误用户名正确点击登陆]', '', i[0])
            # 通过替换统一格式方便后续判断
            x = x.replace('，', ',')
            pos = x.find(',')
            if pos >= 0:
                temp.append(x[:pos])
                temp.append(x[pos + 1:-1])

                y = i[1].find('：')
                temp.append(i[1][y + 1:])
                result.append(temp)
        print(result)

    def Buy(self):
        lis = []
        result = []
        # 备表
        data_path = self.xls_path()
        data = dtR.xls(data_path, 2)
        # 读表
        xls = data.read(3)
        # 提取有效数据
        for i in xls:
            lis.append([i[3], i[5]])
        # print(lis)
        # 清理数据格式并重新封装
        for i in lis:
            temp = []
            # 通过re表达式过滤内容
            x = re.sub('[数据库存在用户：余额足够,支付密码假设是第一张默认卡二切换到没有置初始银行]', '', i[0])
            # 通过替换统一格式方便后续判断
            x = x[:x.find('\n')] + x[x.find('，'):]
            x = x.replace('\n', '')
            # print(x)
            pos = x.find('/')
            if pos >= 0:
                temp.append(x[:pos])
                poi = x.find('，')
                temp.append(x[pos + 1:poi])
                if poi != -1:
                    temp.append(x[poi + 1:].strip('，'))
                else:
                    temp.append('')

                y = i[1][i[1].find('：') + 1:]
                poy = i[1].find('【')
                if y.isdigit():
                    temp.append(i[1][:i[1].find('，')])
                    temp.append(y)
                elif poy == -1:
                    msg = i[1][i[1].find('：')+1:].strip('\n')
                    temp.append(msg)
                    temp.append('0')
                else:
                    msg = i[1][i[1].find('：') + 1:poy].strip('\n')
                    temp.append(msg)
                    temp.append('0')
                result.append(temp)
        print(result)

    def Loan(self):
        lis = []
        result = []
        # 备表
        data_path = self.xls_path()
        data = dtR.xls(data_path, 3)
        # 读表
        xls = data.read(2)
        # 提取有效数据
        for i in xls:
            lis.append([i[3], i[4], i[5]])
        # print(lis)
        # 清理数据格式并重新封装
        for i in lis:
            temp = []
            # 通过re表达式过滤内容
            x = re.sub('[数据库存在用户：余额足够,支付密码假设是第一张默认卡二切换到没有置初始银行]', '', i[0])
            y = re.sub('[，]', ' ', i[1])
            y = ''.join(filter(lambda c: ord(c) < 256, y))

            # 通过替换统一格式方便后续判断
            x = x[:x.find('\n')] + x[x.find('，'):]
            x = x.replace('\n', '')
            y = y[y.find('3.')+2:y.find('4.')]
            y = y.replace('\n', '')
            # print(y)
            # print(x)
            pos = x.find('/')
            if pos >= 0:
                temp.append(x[:pos])
                poi = x.find('，')
                temp.append(x[pos + 1:poi])
                temp.append(x[poi + 1:].strip('，'))
                temp.append(y[:y.find(' ')])
                temp.append(y[y.find(' ')+1:].strip())
                temp.append(i[2])

                result.append(temp)
        print(result)

class readyMysql():

    def __init__(self):
        self.mysql = dtR.mysql_process('192.168.75.147', 'finance', password='root')

        self.d = [[['1000', 'lisi3', '测试', 'e10adc3949ba59abbe56e057f20f883e', '100000000000000000', '10000000000', 'test@qq.com', '666666', '0', 'test'],
              ['2000', 'lisi4', '测试', 'e10adc3949ba59abbe56e057f20f883e', '100000000000000000', '10000000000', 'test@qq.com', '666666', '0', 'test'],
              ['3000', 'lisi5', '测试', 'e10adc3949ba59abbe56e057f20f883e', '100000000000000000', '10000000000', 'test@qq.com', '666666', '0', 'test'],
              ['4000', 'lisi6', '测试', 'e10adc3949ba59abbe56e057f20f883e', '100000000000000000', '10000000000', 'test@qq.com', '1', '0', 'test'],
              ['5000', 'lisi7', '测试', 'e10adc3949ba59abbe56e057f20f883e', '100000000000000000', '10000000000', 'test@qq.com', '666666', '0', 'test']],
             [['1000', '测试银行', '2', '1000000000000000', '1000', '1000000', '1'],
              ['2000', '测试银行', '2', '1000000000000001', '2000', '10', '1'],
              ['3000', '测试银行', '2', '1000000000000002', '4000', '10000', '1'],
              ['4000', '测试银行', '2', '1000000000000003', '5000', '10000', '1'],
              ['5000', '测试银行', '2', '1000000000000004', '5000', '10000', '0'],
              ['6000', '测试银行', '2', '1000000000000005', '5000', '10000', '0'], ]]

    def create(self):
        sql_create = ['insert into user values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
                      'insert into bankcard values(%s,%s,%s,%s,%s,%s,%s)']
        self.mysql.up()
        for i in self.d[0]:
            self.mysql.sql(sql_create[0], data=i)
        for i in self.d[1]:
            self.mysql.sql(sql_create[1], data=i)
        self.mysql.down()

    def delete(self):
        sql_clear = ['delete from user where IDcard = "100000000000000000"',
                     'delete from bankcard where cardBank = "测试银行"']
        self.mysql.up()
        for i in sql_clear:
            self.mysql.sql(i)
        self.mysql.down()

    def check_money(self, name):
        sql_money = f"SELECT balance FROM bankcard WHERE userId = (SELECT id FROM user WHERE username='{name}' AND reputation='test') AND defaultl = 1"
        self.mysql.up()
        self.mysql.sql(sql_money)
        try:
            result = self.mysql.check(1)[0]
        except:
            return False
        self.mysql.down()
        return result

